
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteCOI]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteCOI]
GO

CREATE PROCEDURE [dbo].[DeleteCOI]  (
@id 			INTEGER = 0
)

AS

BEGIN
	DECLARE @TempQAIds TABLE(
	[QuestionnaireAnswerId] [int] NOT NULL
	)

	DECLARE @TempDocIds TABLE(
	[DocumentId] [int] NOT NULL
	)

	DELETE Answer WHERE QuestionnaireAnswerId in (SELECT QuestionnaireAnswerId FROM COI_QuestionnaireAnswer_Map
					WHERE COIId = @id )

	INSERT INTO @TempQAIds (QuestionnaireAnswerId)
	SELECT QuestionnaireAnswerId 
	FROM COI_QuestionnaireAnswer_Map
	WHERE COIId = @id

	DELETE COI_QuestionnaireAnswer_Map 
	WHERE COIId = @id

	DELETE QuestionnaireAnswer WHERE Id in (SELECT QuestionnaireAnswerId FROM @TempQAIds)

	INSERT INTO @TempDocIds (DocumentId)
	SELECT DocumentId FROM ConflictOfInterest WHERE Id = @id
	
	DELETE Conflictofinterest WHERE Id = @id
	DELETE Document WHERE Id in (Select DocumentId from @TempDocIds)
	
	IF @@ERROR <> 0
	BEGIN
		RAISERROR('Unable to Delete COI', 16, 1)
		RETURN
	END
		
		
END
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 